import pandas as pd
import numpy as np
import sqlalchemy as sqla
import pyodbc

def convert_str_item_encoding(item):
    if pd.isna(item):
        return item
    else:
       return item.encode('latin-1').decode('GB18030')

def get_data_from_sqlserver(database,table_name):
    db = sqla.create_engine("mssql+pymssql://localhost/{}?tds_version=7.0".format(database))
    df = pd.read_sql("select * from {}".format(table_name), db)
    # for i,col_type in enumerate(df.dtypes):
    #     if type(col_type) == np.dtypes.ObjectDType:
    #         col_label = df.columns[i]
    #         if not df[col_label].empty:
    #             df[col_label] = df[col_label].apply(convert_str_item_encoding)
    return df

def sent_data_to_sqlserver(database,table_name,df):
    db = sqla.create_engine("mssql+pymssql://localhost/{}?tds_version=7.0".format(database))
    df.to_sql(table_name,db,if_exists='replace',index=False)

def get_dataframe_from_sqlserver(database,table_name):
    connect_str = 'DRIVER={SQL Server};SERVER=localhost;DATABASE=%s;Trusted_Connection=yes'%database
    conn = pyodbc.connect(connect_str)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM {}".format(table_name))
    column_names = [column[0] for column in cursor.description]
    data = cursor.fetchall()
    df = pd.DataFrame(np.array(data),columns=column_names)
    conn.close()
    return df